package analytics;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import sklad.StoreEnum;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * User: Vladimir
 * Date: 03.08.12 Time: 20:47
 */
public class Sells {
    static List<Product> products = new ArrayList<Product>();
    static Map<String, List<Product>> productsByInterval = new HashMap<String, List<Product>>();
    static SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy");

    public static void main(String[] args) throws BiffException, IOException {
        parseProducts("data/sells");
    }

    private static void parseProducts(String path) throws BiffException, IOException {
        File folder = new File(path);
        for (File file : folder.listFiles()) {
            if (file.getName().startsWith(".") || file.getName().startsWith("results")) {
                continue;
            }
            List<Product> productsByFile = parseProductsFile(file);
            productsByInterval.put(file.getName().replace(".xls", ""), productsByFile);
        }
        Collections.sort(products, new Comparator<Product>() {
            @Override
            public int compare(Product o1, Product o2) {
                return new Integer(o2.sellAmount).compareTo(o1.sellAmount);
            }
        });
        int totalAmount = 0;
        double totalPrice = 0;
        for (Product product : products) {
            System.out.println(product);
            totalAmount += product.sellAmount;
            totalPrice += product.priceTotal;
        }
        System.out.println("--------------------------------------");
        System.out.println("Total: " + totalAmount + ", " + totalPrice);
        saveToExcel("data/sells/results.xls", productsByInterval, products);
    }

    private static void saveToExcel(String xlsFile, Map<String, List<Product>> productsByInterval, List<Product> allProducts) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        FileOutputStream fileOut = new FileOutputStream(xlsFile);
        HSSFSheet sheet = wb.createSheet("Статистика");

//        headerRow.createCell(column++).setCellValue("Интервал");
//        headerRow.createCell(column++).setCellValue("Кол-во");
//        headerRow.createCell(column).
//        List<StoreEnum> values = Arrays.asList(StoreEnum.values());
        List<String> intervals = new ArrayList<String>(productsByInterval.keySet());
        Collections.sort(intervals, new Comparator<String>() {
            @Override
            public int compare(String o1, String o2) {
                try {
                    Date date1 = format.parse(o1.split("-")[0]);
                    Date date2 = format.parse(o2.split("-")[0]);
                    return date1.compareTo(date2);
                } catch (ParseException e) {
                    e.printStackTrace();
                    return o1.compareTo(o2);
                }
            }
        });
        int column = 1;
        HSSFRow headerRow = sheet.createRow(0);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, column, column + 1));
        headerRow.createCell(column).setCellValue("Всего");
        for (String interval : intervals) {
            column += 2;
            sheet.addMergedRegion(new CellRangeAddress(0, 0, column, column + 1));
            headerRow.createCell(column).setCellValue(interval);
        }
        int rowNum = 1;
        for (Product product : allProducts) {
            column = 0;
            HSSFRow row = sheet.createRow(rowNum++);
            row.createCell(column++).setCellValue(product.article + " " + product.name);
            row.createCell(column++).setCellValue(product.sellAmount);
            row.createCell(column++).setCellValue(product.priceTotal);
            for (String interval : intervals) {
                List<Product> products = productsByInterval.get(interval);
                if (products.contains(product)) {
                    product = products.get(products.indexOf(product));
                    row.createCell(column++).setCellValue(product.sellAmount);
                    row.createCell(column++).setCellValue(product.priceTotal);
                } else {
                    row.createCell(column++).setCellValue(0);
                    row.createCell(column++).setCellValue(0);
                }
            }
            
        }
        sheet.autoSizeColumn(0);
//        headerRow.createCell(3).setCellValue("Наименование");
        wb.write(fileOut);
        fileOut.close();
    }

    private static List<Product> parseProductsFile(File file) throws BiffException, IOException {
        System.out.println(file);
        Workbook workbook = Workbook.getWorkbook(file);
        Sheet sheet = workbook.getSheet(0);
        List<Product> productsByFile = new ArrayList<Product>();
        boolean startData = false;
        for (int row = 0; row < sheet.getRows(); row++) {
            if (!startData && sheet.getCell(0, row).getContents().equals("Наименование")) {
                startData = true;
                continue;
            } else if (startData && sheet.getCell(0, row).getContents().equals("Итог")) {
                break;
            }
            if (startData) {
                Product product = new Product();
                String[] fields = sheet.getCell(0, row).getContents().split(",")[0].split(" ");
                String article = fields[0];
                String name = "";
                for (int i = 1; i < fields.length; i++) {
                    name = name + " " + fields[i];
                }
//                System.out.println(article + " " + name);
                int amount = Integer.parseInt(sheet.getCell(1, row).getContents());
                double price = Double.parseDouble(sheet.getCell(3, row).getContents().replaceAll("\\,", ".").replace((char) 160, (char) 32).trim().replaceAll("\\s", ""));
                product.article = article;
                if (products.contains(product)) {
                    product = products.get(products.indexOf(product));
                } else {
                    products.add(product);
                }
                if (product.name == null || product.name.length() < 3) {
                    product.name = name;
                }
                product.sellAmount += product.sellAmount + amount;
                product.priceTotal += product.priceTotal + price;

                Product productByFile = new Product();
                productByFile.name = name;
                productByFile.article = article;
                productByFile.priceTotal = price;
                productByFile.sellAmount = amount;
                productsByFile.add(productByFile);
            }
        }
        return productsByFile;
    }
}

class Product {
    String article;
    String name;
    int sellAmount;
    int buyAmount;
    double buyPricePerPiece;
    double sellPricePerPiece;
    double priceTotal;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        Product product = (Product) o;

        if (article != null ? !article.equals(product.article) : product.article != null) return false;

        return true;
    }

    @Override
    public int hashCode() {
        return article != null ? article.hashCode() : 0;
    }

    @Override
    public String toString() {
        return article + ", " + name + ", " + sellAmount + ", " + priceTotal;
    }
}
